﻿CREATE PROCEDURE [dbo].[proc_Order_Getlist_Analyse_In]
	(
		@inCompanyId varchar(500),
		@StateId int,
		@Stext nvarchar(50),
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@StartIndex int,
		@EndIndex int
	)
AS
Begin
Declare @cSql varchar(2000)

Set @cSql = 'CompanyId In('+@InCompanyId+')'

if @StateId<>0
	Set @cSql = @cSql + ' And StateId='+Convert(varchar(10),@StateId)

if @Stext<>''
	Set @cSql = @cSql + ' And (CusName like ''%'+@Stext+'%'' Or Salesman like ''%'+@Stext+'%'')'

Set @cSql='Declare @sRq Datetime,@eRq Datetime
Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')
;WITH list As(Select ROW_NUMBER() OVER (Order By FinanceRenlingDate Desc,Id Desc)AS Row
		,Id
		,OId
		,OrderId
		,CusId
		,CusName
		,CusPerson
		,CusPersonTel
		,CusDescription
		,CusEstimate
		,TypeId
		,TypeName
		,WayId
		,WayName
		,ProcessId
		,ProcessName
		,Salesman
		,SalesmanAccount
		,TradeDate
		,CreateDate
		,YingShouPrice
		,ISNULL(ShiShouPrice,0) As ShiShouPrice
		,Discount
		,PaymentTypeId
		,PaymentType
		,BankId
		,Bank
		,PaymentDescription
		,OtherDescription
		,DepId
		,CompanyId
		,StateId
		,IsNULL((Select Title From Order_State Where Id=[Order].StateId),''...'') As StateName
		,FinanceRenlingDate
		,IsGreen
		--,Isnull((select Pizhu from Customer_PiZhu where CustomerId=CusId),'') as Pizhu --批注内容字段 2015.08.20 
		--,Isnull((select CusState from Customer_PiZhu where CustomerId=CusId),0) as CusState --是否有批注字段 
,NOE_Flag
		From [Order]
		Where '+@cSql + '
		And FinanceRenlingDate>=@sRq And FinanceRenlingDate<=@eRq
	)

	Select *,(Select Count(0) From list) As RecordCount From list Where Row Between ' + Convert(varchar(10),@StartIndex)+' And '+ Convert(varchar(10),@EndIndex) + ' Order By Row'
	
	Exec(@cSql)
End
